﻿use SuperMarketManagement
go
--										NhomQuyen
insert into NhomQuyen values ('Admin',0)
insert into NhomQuyen values ('Temp',0)

insert into NhanVien values ('NV_0000',N'Hòa','03/05/1990',0,'PB_0000','','','','','','','10','03/05/2011','','','','','',0)
--										TaiKhoan
insert into TaiKhoan values ('ADMINISTRATOR','ADMINISTRATOR',0,'NV_0000',1,'Admin')
insert into TaiKhoan values ('hoadm','hoadm',0,'NV_0000' ,1,'Admin')

--										Quyen
insert into Quyen values('frmQuanLyKhachHang',N'Quản lý khách hàng')--Bắt đầu 
insert into Quyen values('frmQuanLyHangHoa',N'Quản lý hàng hóa')
insert into Quyen values('frmQuanLyKhuyenMaiTangHang',N'Quản lý khuyến mại tặng hàng')
insert into Quyen values('frmQuanLyLoaiTheVip',N'Quản lý loại thẻ vip')
insert into Quyen values('frmQuanLyLuuVietHeThong',N'Quản lý lưu viết hệ thống')
insert into Quyen values('frmQuanLyPhanQuyen',N'Phân quyền')
insert into Quyen values('frmQuanLySaoLuuHeThong',N'Sao lưu hệ thống')
insert into Quyen values('frmQuanLyTheVip',N'Quản lý thẻ vip')
insert into Quyen values('frmQuanLyTinhDiemThuong',N'Quản lý tính điểm thường')
insert into Quyen values('frmQuanLyTraLaiNCC',N'Quản lý trả lại nhà cung cấp')
insert into Quyen values('frmQuanLyCapNhatGiaHangHoa',N'Quản lý cập nhật giá hàng hóa')

insert into Quyen values('frmQuanLyBanBuon',N'Quản lý bán buôn')
insert into Quyen values('frmBanHangTheoHoaDon',N'Báo cáo bán hàng theo hóa đơn') 
insert into Quyen values('frmBCBanHangTheoNgay',N'Báo cáo bán hàng theo ngày') 
insert into Quyen values('frmBanHangTheoNhanVien',N'Báo cáo bán hàng theo nhân viên') 
insert into Quyen values('frmChiTietBanHangTheoHangHoa',N'Báo cáo chi tiết bán hàng theo hàng hóa') 
insert into Quyen values('frmChiTietBanHangTheoLoaiHang',N'Báo cáo chi tiết bán hàng theo loại hàng') 
insert into Quyen values('frmChiTietBanHangTheoNhomHang',N'Báo cáo chi tiết bán hàng theo nhóm hàng') 
insert into Quyen values('frmChiTietCongNoNCC',N'Báo cáo chi tiết công nợ nhà cung cấp') 
insert into Quyen values('frmKhachHangThanThiet',N'Báo cáo khách hàng thân thiết') 
insert into Quyen values('frmKhuyenMaiGiamGia',N'Báo cáo khuyến mại giảm giá') 
insert into Quyen values('frmKhuyenMaiTangHang',N'Báo cáo khách hàng tặng hàng') 
insert into Quyen values('frmLaiLo',N'Báo cáo lãi lỗ') 
insert into Quyen values('frmTongHopChi',N'Báo cáo tổng hợp phiếu chi') 
insert into Quyen values('frmTongHopCongNoNCC',N'Báo cáo tổng hợp công nợ nhà cung cấp') 
insert into Quyen values('frmTongHopPhieuThu',N'Báo cáo tổng hợp phiếu thu') 
insert into Quyen values('frmTongHopThuChi',N'Báo cáo sổ quỹ tiền mặt') 
insert into Quyen values('frmQuanLyDieuChuyenKho',N'Quản lý điều chuyển kho') 
insert into Quyen values('frmXacNhanPhieuPhieuDieuChuyen',N'Xác nhận điều chuyển kho')
insert into Quyen values('frmSoQuy',N'Sổ quỹ tiền mặt') 
insert into Quyen values('frmKetChuyenSoDuSoQuy',N'Kết chuyển sổ quỹ') 
insert into Quyen values('frmNhapSoDuSoQuy',N'Nhập số dư sổ quỹ') 

--													Hương
insert into Quyen values('frmQuanLyCongTy',N'Quản lý công ty')
insert into Quyen values('frmCongNo',N'Công nợ')
insert into Quyen values('frmKetChuyenSoDuCongNo',N'Kết chuyển sô dư công nợ')
insert into Quyen values('frmNhapSoDuCongNo',N'Nhập số dư công nợ')
insert into Quyen values('frmQuanLyDiaDiem',N'Quản lý địa điểm')
insert into Quyen values('frmQuanLyDVT',N'Quản lý đơn vị tính')
insert into Quyen values('frmQuanLyGoiHang',N'Quản lý gói hàng')
insert into Quyen values('frmQuanLyKhuVuc',N'Quản lý khu vực')
insert into Quyen values('frmQuanLy',N'Quản lý kiểm kê kho ')
insert into Quyen values('frmQuanLyLHH',N'Quản lý loại hàng hóa')
insert into Quyen values('frmQuanLyNCC',N'Quản lý nhà cung cấp')
insert into Quyen values('frmQuanLyPhieuXuatHuy',N'Quản lý phiếu xuất hủy')
insert into Quyen values('frmXacNhanPhieuXuatHuy',N'xác nhận phiếu xuất hủy')
insert into Quyen values('frmQuanLyPhongBan',N'Quản lý phòng ban')
insert into Quyen values('frmQuanLyQuyDoiDVT',N'Quản lý quy đổi đơn vị tính')
insert into Quyen values('frmQuanLyThue',N'Quản lý thuế')
insert into Quyen values('frmQuanLyTienTe',N'Quản lý tiền tệ')
insert into Quyen values('BCTongHopCongNoKH',N'Báo cáo tổng hợp công nợ khách hàng')
insert into Quyen values('BCChiTietCongNoKH',N'Báo cáo chi tiết công nợ khách hàng')
insert into Quyen values('BCXuatHuyHangHoa',N'Báo cáo xuất hủy hàng hóa')
insert into Quyen values('BCPhieuDieuChuyen',N'Báo cáo phiếu điều chuyển kho')


--													Hùng vv
insert into Quyen values('frmHienThiTKNH',N'Hiển thị tài khoản ngân hàng')
insert into Quyen values('frmHienThiTaiKhoanKeToan',N'Quản lý tài khoản kế toán')
insert into Quyen values('frmHienThiNhomTaiKhoanKeToan',N'Quản lý nhóm tài khoản kế toán')
insert into Quyen values('frmHienThiChiTietHangHoaTheoNhom',N'Quản lý chi tiết hàng hóa theo nhóm')
insert into Quyen values('frmQuanLyNhapKho',N'Quản lý nhập kho')
insert into Quyen values('frmHienThiNhanVien',N'Quản lý nhân viên')
insert into Quyen values('frmHienThiKhoanMucThuChi',N'Quản lý khoản mục thu chi')
insert into Quyen values('frmQuanLyKhachHangTraLaiBanLe',N'Quản lý khách hàng trả lại bán lẻ')
insert into Quyen values('frmQuanLyTraLaiBanBuon',N'Quản lý khách hàng trả lại bán buôn')
insert into Quyen values('frmCaiDatKetNoi',N'Cài đặt kết nối')
insert into Quyen values('frmQuanLyMaVach',N'Quản lý mã vạch')
insert into Quyen values('frmQuanLyCamera',N'Quản lý camera')
insert into Quyen values('frmQuanLyCaiDat',N'Quản lý cái đặt')
insert into Quyen values('frmQuanLyBanLe',N'Quản lý bán lẻ')
insert into Quyen values('frmQuanLyBaoCaoChiTietDoanhThuBanHangChoKhachHang',N'Báo cáo chi tiết doanh thu bán hàng theo khách hàng')
insert into Quyen values('frmQuanLyBaoCaoChiTietDoanhThuBanHangTheoNhanVien',N'Báo cáo chi tiết doanh thu bán hàng theo nhân viên')
insert into Quyen values('frmQuanLyBaoCaoChiTietNhapHangTheoNhaCungCap',N'Báo cáo chi tiết nhập hàng theo nhà cung cấp')
insert into Quyen values('frmQuanLyBaoCaoDoanhThuBanHangTheoKhachHang',N'Báo cáo doanh thu bán hàng theo khách hàng')
insert into Quyen values('frmQuanLyBaoCaoTraLaiHang',N'Báo cáo trả lại hàng')
insert into Quyen values('frmQuanLyBaoCaoTraLaiHangNhaCungCap',N'Báo cáo trả lại hàng nhà cung cấp')
insert into Quyen values('frmQuanLyBaoCaoXuatNhapTonTheoNhomHang',N'Báo cáo xuất nhập tồn theo nhóm hàng')
insert into Quyen values('frmHienThiNhomHangHoa',N'Quản lý nhóm hàng')
--											Kiên
insert into Quyen values('frmChuyenSoDuTonKhoCuoiKy',N'Chuyển số dư tồn kho cuối kỳ')
insert into Quyen values('frmNhapSoDuTonKhoDauKy',N'Nhập số dư tồn kho đầu kỳ')
insert into Quyen values('frmTheKho',N'Thẻ kho')
insert into Quyen values('frmTongHopXNT',N'Tổng hợp xuất nhập tồn')
insert into Quyen values('frmBCXuatNhapTonTheoKho',N'Báo cáo xuất nhập tồn theo kho')

--											Lợi
insert into Quyen values('frmQuanLyCSGKM',N'Quản lý chính sách giá khuyến mại')
insert into Quyen values('frmQuanLyDatHangNhaCungCap',N'Quản lý đơn đặt hàng nhà cung cấp')
insert into Quyen values('frmQuanLyKhachHangDatHang',N'Quản lý khách hàng đặt hàng')
insert into Quyen values('frmQuanLyChiTietKhoHang',N'Quản lý chi tiết kho hàng')
insert into Quyen values('frmQuanLyKhoHang',N'Quản lý kho hàng')
insert into Quyen values('frmQuanLyPhieuChi',N'Quản lý phiếu chi')
insert into Quyen values('frmQuanLyPhieuThanhToanKhachHang',N'Quản lý phiếu thanh toán khách hàng')
insert into Quyen values('frmQuanLyPhieuThanhToanNhaCungCap',N'Quản lý phiếu thanh toán nhà cung cấp')
insert into Quyen values('frmQuanLyPhieuThu',N'Quản lý phiếu thu')



--										ChiTietQuyen
insert into ChiTietQuyen values('Admin','frmQuanLyKhachHang',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyHangHoa',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyKhuyenMaiTangHang',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyLoaiTheVip',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyLuuVietHeThong',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyPhanQuyen',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLySaoLuuHeThong',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyTheVip',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyTinhDiemThuong',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyTraLaiNCC',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyCapNhatGiaHangHoa',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyBanBuon',1,1,1,1) 
insert into ChiTietQuyen values('Admin','frmBanHangTheoHoaDon',1,1,1,1) 
insert into ChiTietQuyen values('Admin','frmBCBanHangTheoNgay',1,1,1,1) 
insert into ChiTietQuyen values('Admin','frmBanHangTheoNhanVien',1,1,1,1) 
insert into ChiTietQuyen values('Admin','frmChiTietBanHangTheoHangHoa',1,1,1,1) 
insert into ChiTietQuyen values('Admin','frmChiTietBanHangTheoLoaiHang',1,1,1,1) 
insert into ChiTietQuyen values('Admin','frmChiTietBanHangTheoNhomHang',1,1,1,1) 
insert into ChiTietQuyen values('Admin','frmChiTietCongNoNCC',1,1,1,1) 
insert into ChiTietQuyen values('Admin','frmKhachHangThanThiet',1,1,1,1) 
insert into ChiTietQuyen values('Admin','frmKhuyenMaiGiamGia',1,1,1,1) 
insert into ChiTietQuyen values('Admin','frmKhuyenMaiTangHang',1,1,1,1) 
insert into ChiTietQuyen values('Admin','frmLaiLo',1,1,1,1) 
insert into ChiTietQuyen values('Admin','frmTongHopChi',1,1,1,1) 
insert into ChiTietQuyen values('Admin','frmTongHopCongNoNCC',1,1,1,1) 
insert into ChiTietQuyen values('Admin','frmTongHopPhieuThu',1,1,1,1) 
insert into ChiTietQuyen values('Admin','frmTongHopThuChi',1,1,1,1) 
insert into ChiTietQuyen values('Admin','frmQuanLyDieuChuyenKho',1,1,1,1) 
insert into ChiTietQuyen values('Admin','frmXacNhanPhieuPhieuDieuChuyen',1,1,1,1) 
insert into ChiTietQuyen values('Admin','frmQuanLyKhuyenMaiTangHang',1,1,1,1) 
insert into ChiTietQuyen values('Admin','frmSoQuy',1,1,1,1) 
insert into ChiTietQuyen values('Admin','frmKetChuyenSoDuSoQuy',1,1,1,1) 
insert into ChiTietQuyen values('Admin','frmNhapSoDuSoQuy',1,1,1,1) 

--													Hương
insert into ChiTietQuyen values('Admin','frmQuanLyCongTy',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmCongNo',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmKetChuyenSoDuCongNo',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmNhapSoDuCongNo',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyDiaDiem',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyDVT',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyGoiHang',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyKhuVuc',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLy',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyLHH',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyNCC',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyPhieuXuatHuy',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmXacNhanPhieuXuatHuy',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyPhongBan',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyQuyDoiDVT',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyThue',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyTienTe',1,1,1,1)
insert into ChiTietQuyen values('Admin','BCTongHopCongNoKH',1,1,1,1)
insert into ChiTietQuyen values('Admin','BCChiTietCongNoKH',1,1,1,1)
insert into ChiTietQuyen values('Admin','BCXuatHuyHangHoa',1,1,1,1)
insert into ChiTietQuyen values('Admin','BCPhieuDieuChuyen',1,1,1,1)
--													Hùng vv
insert into ChiTietQuyen values('Admin','frmHienThiTKNH',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmHienThiTaiKhoanKeToan',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmHienThiNhomTaiKhoanKeToan',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmHienThiChiTietHangHoaTheoNhom',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyNhapKho',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmHienThiNhanVien',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmHienThiKhoanMucThuChi',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyKhachHangTraLaiBanLe',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyTraLaiBanBuon',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmCaiDatKetNoi',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyMaVach',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyCamera',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyCaiDat',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyBanLe',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyBaoCaoChiTietDoanhThuBanHangChoKhachHang',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyBaoCaoChiTietDoanhThuBanHangTheoNhanVien',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyBaoCaoChiTietNhapHangTheoNhaCungCap',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyBaoCaoDoanhThuBanHangTheoKhachHang',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyBaoCaoTraLaiHang',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyBaoCaoTraLaiHangNhaCungCap',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyBaoCaoXuatNhapTonTheoNhomHang',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmHienThiNhomHangHoa',1,1,1,1)
--											Kiên
insert into ChiTietQuyen values('Admin','frmChuyenSoDuTonKhoCuoiKy',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmNhapSoDuTonKhoDauKy',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmTheKho',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmTongHopXNT',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmBCXuatNhapTonTheoKho',1,1,1,1)
--											Lợi
insert into ChiTietQuyen values('Admin','frmQuanLyCSGKM',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyDatHangNhaCungCap',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyKhachHangDatHang',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyChiTietKhoHang',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyKhoHang',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyPhieuChi',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyPhieuThanhToanKhachHang',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyPhieuThanhToanNhaCungCap',1,1,1,1)
insert into ChiTietQuyen values('Admin','frmQuanLyPhieuThu',1,1,1,1)
go
--														
--														Phân Quyền
create proc sp_LogIn
@TK nvarchar(200),
@MK nvarchar(200)
as
select * from dbo.TaiKhoan join dbo.NhanVien on dbo.TaiKhoan.NhanVienID=dbo.NhanVien.MaNhanVien
where dbo.TaiKhoan.KhoaTaiKhoan=0 and dbo.TaiKhoan.TenDangNhap=@TK and dbo.TaiKhoan.MatKhauDangNhap=@MK 
go
--exec sp_LogIn 'huongnt','huongnt'
go
--							Lấy tất cả tài khoản
create proc sp_selectTaiKhoan 
as
select * from dbo.TaiKhoan join dbo.NhanVien on dbo.TaiKhoan.NhanVienID=dbo.NhanVien.MaNhanVien
go
--							Lấy Tất cả nhóm Quyền
create proc sp_selectNhomQuyen
as
select * from dbo.NhomQuyen
go
--							Lấy Chi Tiết Quyền
create proc sp_selectChiTietQuyen
@TenNhomQuyen nvarchar(50)
as
select * from dbo.ChiTietQuyen join dbo.Quyen on dbo.ChiTietQuyen.TenForm=dbo.Quyen.TenForm where TenNhomQuyen=@TenNhomQuyen
go
--							Sửa Chi Tiết Quyền
create proc sp_updateChiTietQuyen
@TenNhomQuyen nvarchar(50),
@TenForm nvarchar(50),
@QuyenThem BIT ,
@QuyenSua BIT ,
@QuyenXoa BIT ,
@QuyenXem BIT 
as
update dbo.ChiTietQuyen set
QuyenThem=@QuyenThem,
QuyenSua=@QuyenSua,
QuyenXoa=@QuyenXoa,
QuyenXem=@QuyenXem
where TenNhomQuyen=@TenNhomQuyen and TenForm=@TenForm
GO
--								Thêm Nhóm Quyền
create proc sp_insertNhomQuyen
@TenNhomQuyen nvarchar(50)
as
delete from ChiTietQuyen where TenNhomQuyen=@TenNhomQuyen
insert into dbo.NhomQuyen values(@TenNhomQuyen,1)
declare @sodem int
set @sodem= (select top(1)QuyenID from Quyen order by QuyenID desc)
while(@sodem>0)
begin
	declare @TenForm nvarchar(50)
	if(exists(select QuyenID from Quyen where QuyenID=@sodem))
	begin
		set @TenForm=(select TenForm from Quyen where QuyenID=@sodem)
		insert into dbo.ChiTietQuyen values(@TenNhomQuyen,@TenForm,0,0,0,0)
	end
	set @sodem=@sodem-1
end
if((select count(TenNhomQuyen) from NhomQuyen where TenNhomQuyen='Temp' )=0)
begin
	insert into NhomQuyen values('Temp',0)
end

go
--exec sp_insertNhomQuyen 'test'
go
--									Thêm Tài Khoản
create proc sp_insertTaiKhoan
@TenDangNhap nVARCHAR(200) ,
@MatKhauDangNhap nVARCHAR(200),
@KhoaTaiKhoan BIT,
@NhanVienID nVARCHAR(20),
@Administrator bit,
@TenNhomQuyen nvarchar(50)
as 
insert into dbo.TaiKhoan values(@TenDangNhap,@MatKhauDangNhap,@KhoaTaiKhoan,@NhanVienID,@Administrator,@TenNhomQuyen)
go
--									Sửa Tài Khoản
create proc sp_updateTaiKhoan
@TenDangNhap nVARCHAR(200) ,
@MatKhauDangNhap nVARCHAR(200),
@KhoaTaiKhoan BIT,
@NhanVienID nVARCHAR(20),
@Administrator bit,
@TenNhomQuyen nvarchar(50)
as 
update dbo.TaiKhoan set
MatKhauDangNhap=@MatKhauDangNhap,
KhoaTaiKhoan=@KhoaTaiKhoan,
NhanVienID=@NhanVienID,
Administrator=@Administrator,
TenNhomQuyen=@TenNhomQuyen
where TenDangNhap=@TenDangNhap
go
--							Xóa Tài Khoản
create proc sp_deleteTaiKhoan
@TenDangNhap nvarchar(200)
as
delete from dbo.TaiKhoan where TenDangNhap=@TenDangNhap
go
--							Xóa Nhóm Quyền
create proc sp_deleteNhomQuyen
@TenNhomQuyen nvarchar(50)
as
delete from dbo.ChiTietQuyen where TenNhomQuyen=@TenNhomQuyen
update dbo.TaiKhoan set
TenNhomQuyen='Temp'
where TenNhomQuyen=@TenNhomQuyen
delete from dbo.NhomQuyen where TenNhomQuyen=@TenNhomQuyen
go
--exec sp_deleteNhomQuyen 'test'
--							Lấy 1 Nhóm Quyền
create proc sp_selectNhomQuyenTheoMa
@TenNhomQuyen nvarchar(50),
@out bit output
as
begin
declare @so int
set @so=(select COUNT(*) from NhomQuyen where TenNhomQuyen=@TenNhomQuyen)
if(@so!= 0)
begin
	set @out=0
end
else
begin
	set @out=1
end
return
end

----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
--														Hàng Hóa
GO

--region [dbo].[sp_InsertHangHoa]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Gray using CodeSmith 5.0.0.0
-- Template:       StoredProcedures.cst
-- Procedure Name: [dbo].[sp_InsertHangHoa]
-- Date Generated: Tuesday, July 19, 2011
------------------------------------------------------------------------------------------------------------------------

create PROCEDURE [dbo].[sp_InsertHangHoa]
	@MaHangHoa varchar(50),
	@MaNhomHang varchar(50),
	@TenNhomHang nvarchar(200),
	@TenHangHoa nvarchar(200),
	@MaDonViTinh varchar(50),
	@GiaNhap float,
	@GiaBanBuon float,
	@GiaBanLe float,
	@MaThue varchar(50),
	@GiaTriThue float,
	@MucDatHang int,
	@MucTonToiThieu int,
	@LinkImage nvarchar(200),
	@GhiChu nvarchar(200),
	@Deleted bit
AS


INSERT INTO [dbo].[HangHoa] (
	[MaHangHoa],
	[MaNhomHang],
	[TenNhomHang],
	[TenHangHoa],
	[MaDonViTinh],
	[GiaNhap],
	[GiaBanBuon],
	[GiaBanLe],
	[MaThue],
	[giaTriThue],
	[MucDatHang],
	[MucTonToiThieu],
	[LinkImage],
	[GhiChu],
	[Deleted]
) VALUES (
	@MaHangHoa,
	@MaNhomHang,
	@TenNhomHang,
	@TenHangHoa,
	@MaDonViTinh,
	@GiaNhap,
	@GiaBanBuon,
	@GiaBanLe,
	@MaThue,
	@GiaTriThue,
	@MucDatHang,
	@MucTonToiThieu,
	@LinkImage,
	@GhiChu,
	@Deleted
)


--endregion

GO

--region [dbo].[sp_UpdateHangHoa]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Gray using CodeSmith 5.0.0.0
-- Template:       StoredProcedures.cst
-- Procedure Name: [dbo].[sp_UpdateHangHoa]
-- Date Generated: Tuesday, July 19, 2011
------------------------------------------------------------------------------------------------------------------------

create PROCEDURE [dbo].[sp_UpdateHangHoa]
	@MaHangHoa varchar(50),
	@MaNhomHang varchar(50),
	@TenNhomHang nvarchar(200),
	@TenHangHoa nvarchar(200),
	@MaDonViTinh varchar(50),
	@GiaNhap float,
	@GiaBanBuon float,
	@GiaBanLe float,
	@MaThue varchar(50),
	@GiaTriThue float,
	@MucDatHang int,
	@MucTonToiThieu int,
	@LinkImage nvarchar(200),
	@GhiChu nvarchar(200)
AS


UPDATE [dbo].[HangHoa] SET
	
	[MaNhomHang] = @MaNhomHang,
	[TenNhomHang] = @TenNhomHang,
	[TenHangHoa] = @TenHangHoa,
	[MaDonViTinh] = @MaDonViTinh,
	[GiaNhap] = @GiaNhap,
	[GiaBanBuon] = @GiaBanBuon,
	[GiaBanLe] = @GiaBanLe,
	[MaThue] = @MaThue,
	[GiaTriThue] = @GiaTriThue,
	[MucDatHang] = @MucDatHang,
	[MucTonToiThieu] = @MucTonToiThieu,
	[LinkImage] = @LinkImage,
	[GhiChu] = @GhiChu
WHERE
	[MaHangHoa] = @MaHangHoa

--endregion

GO

--region [dbo].[sp_DeleteHangHoa]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Gray using CodeSmith 5.0.0.0
-- Template:       StoredProcedures.cst
-- Procedure Name: [dbo].[sp_DeleteHangHoa]
-- Date Generated: Tuesday, July 19, 2011
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[sp_DeleteHangHoa]
	@MaHangHoa varchar(50)
AS


--DELETE FROM [dbo].[HangHoa]
--WHERE
--	[HangHoaID] = @HangHoaID
update HangHoa set
Deleted=1
where [MaHangHoa] = @MaHangHoa


--endregion

GO

--region [dbo].[sp_SelectHangHoa]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Gray using CodeSmith 5.0.0.0
-- Template:       StoredProcedures.cst
-- Procedure Name: [dbo].[sp_SelectHangHoa]
-- Date Generated: Tuesday, July 19, 2011
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[sp_SelectHangHoa]
	@MaHangHoa varchar(50)
AS

 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	*
FROM
	[dbo].[HangHoa]
WHERE
	[MaHangHoa] = @MaHangHoa

--endregion

GO
create proc sp_SelectHangHoaTheoKho
@MaHangHoa varchar(50),
@MaKho varchar(50)
as
	Select HangHoa.* from HangHoa join ChiTietKhoHang on HangHoa.MaHangHoa=ChiTietKhoHang.MaHangHoa
where 
	HangHoa.MaHangHoa=@MaHangHoa and ChiTietKhoHang.MaKho=@MaKho
	go
--region [dbo].[sp_SelectHangHoasAll]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Gray using CodeSmith 5.0.0.0
-- Template:       StoredProcedures.cst
-- Procedure Name: [dbo].[sp_SelectHangHoasAll]
-- Date Generated: Tuesday, July 19, 2011
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[sp_SelectHangHoasAll]
AS

 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	*
FROM
	[dbo].[HangHoa]

--endregion

GO

-------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------- Cập Nhật Giá Hàng Hóa----------------------------------------------
--region Drop Existing Procedures

--endregion

GO

--region [dbo].[sp_InsertCapNhatGiaHangHoa]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Gray using CodeSmith 5.0.0.0
-- Template:       StoredProcedures.cst
-- Procedure Name: [dbo].[sp_InsertCapNhatGiaHangHoa]
-- Date Generated: Thursday, July 21, 2011
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[sp_InsertCapNhatGiaHangHoa]
	@MaCapNhatGiaHangHoa varchar(50),
	@NgayCapNhat datetime,
	@NhanVien varchar(50),
	@GhiChu nvarchar(200),
	@Deleted bit
AS

INSERT INTO [dbo].[CapNhatGiaHangHoa] (
	[MaCapNhatGiaHangHoa],
	[NgayCapNhat],
	[NhanVien],
	[GhiChu],
	[Deleted]
) VALUES (
	@MaCapNhatGiaHangHoa,
	@NgayCapNhat,
	@NhanVien,
	@GhiChu,
	@Deleted
)

--endregion

GO

--region [dbo].[sp_UpdateCapNhatGiaHangHoa]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Gray using CodeSmith 5.0.0.0
-- Template:       StoredProcedures.cst
-- Procedure Name: [dbo].[sp_UpdateCapNhatGiaHangHoa]
-- Date Generated: Thursday, July 21, 2011
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[sp_UpdateCapNhatGiaHangHoa]
	@MaCapNhatGiaHangHoa varchar(50),
	@NgayCapNhat datetime,
	@NhanVien varchar(50),
	@GhiChu nvarchar(200)
AS


UPDATE [dbo].[CapNhatGiaHangHoa] SET
	[NgayCapNhat] = @NgayCapNhat,
	[NhanVien] = @NhanVien,
	[GhiChu] = @GhiChu
WHERE
	[MaCapNhatGiaHangHoa] = @MaCapNhatGiaHangHoa

--endregion

GO

--region [dbo].[sp_DeleteCapNhatGiaHangHoa]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Gray using CodeSmith 5.0.0.0
-- Template:       StoredProcedures.cst
-- Procedure Name: [dbo].[sp_DeleteCapNhatGiaHangHoa]
-- Date Generated: Thursday, July 21, 2011
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[sp_DeleteCapNhatGiaHangHoa]
	@MaCapNhatGiaHangHoa varchar(50)
AS

DELETE FROM [dbo].[CapNhatGiaHangHoa]
WHERE
	[MaCapNhatGiaHangHoa] = @MaCapNhatGiaHangHoa

--endregion

GO

--region [dbo].[sp_SelectCapNhatGiaHangHoa]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Gray using CodeSmith 5.0.0.0
-- Template:       StoredProcedures.cst
-- Procedure Name: [dbo].[sp_SelectCapNhatGiaHangHoa]
-- Date Generated: Thursday, July 21, 2011
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[sp_SelectCapNhatGiaHangHoa]
	@MaCapNhatGiaHangHoa varchar(50)
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	*
FROM
	[dbo].[CapNhatGiaHangHoa]
WHERE
	[MaCapNhatGiaHangHoa] = @MaCapNhatGiaHangHoa

--endregion

GO

--region [dbo].[sp_SelectCapNhatGiaHangHoasAll]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Gray using CodeSmith 5.0.0.0
-- Template:       StoredProcedures.cst
-- Procedure Name: [dbo].[sp_SelectCapNhatGiaHangHoasAll]
-- Date Generated: Thursday, July 21, 2011
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[sp_SelectCapNhatGiaHangHoasAll]
AS

 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	*
FROM
	[dbo].[CapNhatGiaHangHoa]

--endregion

GO

------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------Chi Tiết Cập Nhật Giá Hàng Hóa-------------------------------------------
--region Drop Existing Procedures

--endregion

GO

--region [dbo].[sp_InsertChiTietCapNhatGiaHangHoa]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Gray using CodeSmith 5.0.0.0
-- Template:       StoredProcedures.cst
-- Procedure Name: [dbo].[sp_InsertChiTietCapNhatGiaHangHoa]
-- Date Generated: Thursday, July 21, 2011
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[sp_InsertChiTietCapNhatGiaHangHoa]
	@MaCapNhatGiaHangHoa varchar(50),
	@MaHangHoa varchar(50),
	@TenHangHoa nvarchar(200),
	@GiaNhap float,
	@GiaBanBuon float,
	@GiaBanLe float,
	@Deleted bit
AS

 

INSERT INTO [dbo].[ChiTietCapNhatGiaHangHoa] (
	[MaCapNhatGiaHangHoa],
	[MaHangHoa],
	[TenHangHoa],
	[GiaNhap],
	[GiaBanBuon],
	[GiaBanLe],
	[Deleted]
) VALUES (
	@MaCapNhatGiaHangHoa,
	@MaHangHoa,
	@TenHangHoa,
	@GiaNhap,
	@GiaBanBuon,
	@GiaBanLe,
	@Deleted
)


--endregion

GO

--region [dbo].[sp_UpdateChiTietCapNhatGiaHangHoa]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Gray using CodeSmith 5.0.0.0
-- Template:       StoredProcedures.cst
-- Procedure Name: [dbo].[sp_UpdateChiTietCapNhatGiaHangHoa]
-- Date Generated: Thursday, July 21, 2011
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[sp_UpdateChiTietCapNhatGiaHangHoa]
	@MaCapNhatGiaHangHoa varchar(50),
	@MaHangHoa varchar(50),
	@TenHangHoa nvarchar(200),
	@GiaNhap float,
	@GiaBanBuon float,
	@GiaBanLe float
AS

 

UPDATE [dbo].[ChiTietCapNhatGiaHangHoa] SET
	
	[MaHangHoa] = @MaHangHoa,
	[TenHangHoa] = @TenHangHoa,
	[GiaNhap] = @GiaNhap,
	[GiaBanBuon] = @GiaBanBuon,
	[GiaBanLe] = @GiaBanLe
WHERE
	[MaCapNhatGiaHangHoa] = @MaCapNhatGiaHangHoa

--endregion

GO

--region [dbo].[sp_DeleteChiTietCapNhatGiaHangHoa]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Gray using CodeSmith 5.0.0.0
-- Template:       StoredProcedures.cst
-- Procedure Name: [dbo].[sp_DeleteChiTietCapNhatGiaHangHoa]
-- Date Generated: Thursday, July 21, 2011
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[sp_DeleteChiTietCapNhatGiaHangHoa]
	@MaCapNhatGiaHangHoa varchar(50)
AS

 

DELETE FROM [dbo].[ChiTietCapNhatGiaHangHoa]
WHERE
	[MaCapNhatGiaHangHoa] = @MaCapNhatGiaHangHoa

--endregion

GO

--region [dbo].[sp_SelectChiTietCapNhatGiaHangHoa]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Gray using CodeSmith 5.0.0.0
-- Template:       StoredProcedures.cst
-- Procedure Name: [dbo].[sp_SelectChiTietCapNhatGiaHangHoa]
-- Date Generated: Thursday, July 21, 2011
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[sp_SelectChiTietCapNhatGiaHangHoa]
	@MaCapNhatGiaHangHoa varchar(50)
AS

 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	*
FROM
	[dbo].[ChiTietCapNhatGiaHangHoa]
WHERE
	[MaCapNhatGiaHangHoa] = @MaCapNhatGiaHangHoa

--endregion

GO

--region [dbo].[sp_SelectChiTietCapNhatGiaHangHoasAll]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Gray using CodeSmith 5.0.0.0
-- Template:       StoredProcedures.cst
-- Procedure Name: [dbo].[sp_SelectChiTietCapNhatGiaHangHoasAll]
-- Date Generated: Thursday, July 21, 2011
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[sp_SelectChiTietCapNhatGiaHangHoasAll]
AS

 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	*
FROM
	[dbo].[ChiTietCapNhatGiaHangHoa]

--endregion


go
--								Cập Nhật Giá Hàng Hóa
create proc sp_updateGiaHH
@MaHangHoa varchar(50),
@GiaNhap float,
@GiaBanBuon float,
@GiaBanLe float
as
begin 
update HangHoa set
GiaNhap=@GiaNhap,
GiaBanBuon=@GiaBanBuon,
GiaBanLe=@GiaBanLe
where 
	MaHangHoa=@MaHangHoa
end
go
create trigger trg_deleteCapNhatGia
on CapNhatGiaHangHoa
instead of delete
as
begin 
declare @ma varchar(50)
set @ma=(select MaCapNhatGiaHangHoa from deleted)
delete from ChiTietCapNhatGiaHangHoa where MaCapNhatGiaHangHoa=@ma
delete from CapNhatGiaHangHoa where MaCapNhatGiaHangHoa=@ma
end
go
